package bookstore.dao;

import bookstore.idao.ICustomerDao;
import bookstore.model.Customer;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class CustomerDao implements ICustomerDao {

    @Override
    public Customer find(String name, String password) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        Customer customer = null;
        try {
            con = MySQLHelper.connect();
            String sql = "select * from BS_CUSTOMER where cust_name=\'"+name+"\' and cust_password=\'"+password+"\'";
            st=con.createStatement();
            rs =st.executeQuery(sql);
            if (rs.next()) {
                customer = new Customer();
                customer.setId(rs.getInt("cust_id"));
                customer.setName(rs.getString("cust_name"));
                customer.setPassword(rs.getString("cust_password"));
                customer.setRealName(rs.getString("cust_realname"));
                customer.setPhone(rs.getString("cust_phone"));
                customer.setEmail(rs.getString("cust_email"));
                customer.setAddress(rs.getString("cust_address"));
                customer.setZipcode(rs.getString("cust_zipcode"));
                customer.setDatetime(rs.getDate("cust_datetime"));
            }
        } catch (Exception e) {
        } finally {
            MySQLHelper.closeResult(rs);
            MySQLHelper.closeConneciton(con);
        }
        return customer;
    }

    @Override
    public void update(Customer customer) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = MySQLHelper.connect();
            String sql = "update bs_customer set cust_name=?,cust_password=?,cust_realname=?,cust_phone=?,cust_email=?,cust_address=?,cust_zipcode=? where cust_id=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, customer.getName());
            ps.setString(2, customer.getPassword());
            ps.setString(3, customer.getRealName());
            ps.setString(4, customer.getPhone());
            ps.setString(5, customer.getEmail());
            ps.setString(6, customer.getAddress());
            ps.setString(7, customer.getZipcode());
            ps.setString(8, customer.getName());
            ps.setInt(9, customer.getId());
            ps.executeUpdate();
        } catch (Exception e) {
        } finally {
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
    }
    public void updateMessage(Customer customer){
        Connection con =null;
         PreparedStatement ps = null;
        try {
            con = MySQLHelper.connect();
            String sql = "update bs_customer set cust_name=?,cust_realname=?,cust_phone=?,cust_email=?,cust_address=?,cust_zipcode=? where cust_id=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, customer.getName());
            ps.setString(2, customer.getRealName());
            ps.setString(3, customer.getPhone());
            ps.setString(4, customer.getEmail());
            ps.setString(5, customer.getAddress());
            ps.setString(6, customer.getZipcode());
            ps.setInt(7, customer.getId());
            ps.executeUpdate();
        } catch (Exception e) {
        } finally {
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
    }
    public void updatePassword(Customer customer)
    {
         Connection con =null;
         PreparedStatement ps = null;
        try {
            con = MySQLHelper.connect();
            String sql = "update bs_customer set cust_password=? where cust_id=?";
            ps = con.prepareStatement(sql);
             ps.setString(1, customer.getPassword());
            ps.setInt(2, customer.getId());
            ps.executeUpdate();
        } catch (Exception e) {
        } finally {
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
    }
    @Override
    public void insert(Customer customer) {
        Connection con = null;
        PreparedStatement ps = null;
        int id=findAllCount()+1;
        try {
            con = MySQLHelper.connect();
            String sql = "insert into bs_customer(cust_name,cust_password,cust_realname,cust_phone,cust_email,cust_address,cust_zipcode,cust_id) values(?,?,?,?,?,?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, customer.getName());
            ps.setString(2, customer.getPassword());
            ps.setString(3, customer.getRealName());
            ps.setString(4, customer.getPhone());
            ps.setString(5, customer.getEmail());
            ps.setString(6, customer.getAddress());
            ps.setString(7, customer.getZipcode());
            ps.setString(8, ""+id);
            ps.executeUpdate();
        } catch (Exception e) {
        } finally {
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
    }

    @Override
    public void delete(Integer id) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = MySQLHelper.connect();
            String sql = "delete from bs_customer where cust_id=?";
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (Exception e) {
        } finally {
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
    }

    @Override
    public Customer findById(Integer id) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Customer customer = null;
        try {
            con = MySQLHelper.connect();
            String sql = "select * from bs_customers where cust_name=?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                customer = new Customer();
                customer.setId(rs.getInt("cust_id"));
                customer.setName(rs.getString("cust_name"));
                customer.setPassword(rs.getString("cust_password"));
                customer.setRealName(rs.getString("cust_realname"));
                customer.setPhone(rs.getString("cust_phone"));
                customer.setEmail(rs.getString("cust_email"));
                customer.setAddress(rs.getString("cust_address"));
                customer.setZipcode(rs.getString("cust_zipcode"));
                customer.setDatetime(rs.getDate("cust_datetime"));
            }
        } catch (Exception e) {
        } finally {
            MySQLHelper.closeResult(rs);
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
        return customer;
    }

    @Override
    public List<Customer> findAll() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Customer> list = new ArrayList<Customer>();
        try {
            con = MySQLHelper.connect();
            String sql = "select * from bs_customer";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            Customer customer = null;
            while (rs.next()) {
                customer = new Customer();
                customer.setId(rs.getInt("cust_id"));
                customer.setName(rs.getString("cust_name"));
                customer.setPassword(rs.getString("cust_password"));
                customer.setRealName(rs.getString("cust_realname"));
                customer.setPhone(rs.getString("cust_phone"));
                customer.setEmail(rs.getString("cust_email"));
                customer.setAddress(rs.getString("cust_address"));
                customer.setZipcode(rs.getString("cust_zipcode"));
                customer.setDatetime(rs.getDate("cust_datetime"));
                list.add(customer);
            }
        } catch (Exception e) {
        } finally {
            MySQLHelper.closeResult(rs);
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
        return list;
    }

    @Override
    public List<Customer> findByPage(Integer pageSize, Integer pageNo) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Customer> list = new ArrayList<Customer>();
        try {
            con = MySQLHelper.connect();
            String sql = "select * from bs_customer limit " + (pageNo - 1) * pageSize + "," + pageSize;
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            Customer customer = null;
            while (rs.next()) {
                customer = new Customer();
                customer.setId(rs.getInt("cust_id"));
                customer.setName(rs.getString("cust_name"));
                customer.setPassword(rs.getString("cust_password"));
                customer.setRealName(rs.getString("cust_realname"));
                customer.setPhone(rs.getString("cust_phone"));
                customer.setEmail(rs.getString("cust_email"));
                customer.setAddress(rs.getString("cust_address"));
                customer.setZipcode(rs.getString("cust_zipcode"));
                customer.setDatetime(rs.getDate("cust_datetime"));
                list.add(customer);
            }
        } catch (Exception e) {
        } finally {
            MySQLHelper.closeResult(rs);
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
        return list;
    }

    @Override
    public int findAllCount() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        try {
            con = MySQLHelper.connect();
            String sql = "select count(*) as count from bs_customer";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (Exception e) {
        } finally {
            MySQLHelper.closeResult(rs);
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
        return count;
    }
}
